﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Threading;
using System.Windows.Forms;
using SYcwpb.Helpers;
using System.Collections;
using System.IO;

namespace SYcwpb.Helpers
{

    class CSVHelper
    {
        public string StrCon { get; set; }   //CSV文件链接字符串

        public string FilePath { get; set; }  //文件路径，不包括文件名

        public string FileName { get; set; }  //文件名

        private string sheetName = string.Empty;  //CSV中的sheet表名

        private static int TempTableColumnsNum = 8;   //每个车次号对应8列属性

        public static string TempTimeTableName ;  //数据库临时表名，两条线，分别用不同的临时表
        
        private static string[] TorbitName;

        private string LineID = CONSTDATA.UserModel.Line;

        SqlDataLayer sqlHelper = new SqlDataLayer();

        private string[] columnsName = { "TrainID", "Trip", "DID", "LOC", "Arrive", "Depart", "Rtime", "DWell" };  //与CSV时刻表列属性对应

        public event EventHandler<InsertProcessEventArgs> InsertProcessEvent;
        public event EventHandler InsertFinishedEvent;

        public CSVHelper(string filepath, string filename)
        {
            FilePath = filepath;
            string[] nameArr = filename.Split('.');
            FileName = nameArr[0];

            StrCon = GetCsvConnStr(FilePath);

            TorbitName = GetTorbit();

            if (LineID == "1")
                TempTimeTableName = "TempTimeTable1";
            else
                TempTimeTableName = "TempTimeTable2";
        }

        /// <summary>
        /// 创建一个临时表，保存CSV文件的原始数据
        /// </summary>
        /// <param name="SQLConnString"></param>
        public void TransferCSVToDB(string SQLConnString,int CSVID)
        {
            //OdbcConnection ODBCconn = new OdbcConnection(StrCon);
            //DataTable Temptable = new DataTable();
            //OdbcDataAdapter dataAdapter = new OdbcDataAdapter();
            string exTrain = string.Empty, exTrip = string.Empty;
            int x = 0;
            int y = 0;
            try
            {
                //ODBCconn.Open();
                //Temptable = ODBCconn.GetSchema("columns", null);    
                //sheetName = Temptable.Rows[0]["Table_name"].ToString();
                //修改这里是因为，该StrCon连接串会打开目录下所有后缀为asc,csv,tab,txt的文件，从而导致打开的文件不正确

                sheetName = FileName + ".csv";
                string Csvcom = "select * from [" + sheetName + "]";

                //DataSet ds = new DataSet();
                //dataAdapter = new OdbcDataAdapter(Csvcom, StrCon);

                //dataAdapter.Fill(ds);

                //DataTable dt = ds.Tables[0];
                DataTable dt = csv2dt(FilePath + "\\" + FileName + ".csv", 0);

                int columnsCount = dt.Columns.Count;

                CreatTempTable();   //如果没有临时表，创建之
                CleanTempTable();  //清空临时表数据

                //CSV 导入DT 时由于类型不一致，会有部分数据丢失，此处作处理；并且将为空的项填满
                int trainIDIndex = 0; int tripIndex = 1; int didIndex = 2; int locIndex = 3;
                int lastTrip = 0;
                for (; tripIndex < columnsCount; )
                {
                    y = tripIndex;
                    int did = Convert.ToInt32(dt.Rows[0][didIndex].ToString());
                    int trip;//1FT处理
                    
                    //一般出段理解为NDOP1，但是备用车有可能从NDOP2出来，反向出段
                    if (LineID == "1")
                    {
                        if (dt.Rows[0][trainIDIndex + 3].ToString() == TorbitName[0] || dt.Rows[0][trainIDIndex + 3].ToString() == TorbitName[1])
                        {
                            trip = 2;
                        }
                        else { trip = 1; }
                    }
                    else //2号线
                    {
                        if (dt.Rows[0][trainIDIndex + 3].ToString() != TorbitName[0] && dt.Rows[0][trainIDIndex + 3].ToString() != TorbitName[1])
                            trip = 2;
                        else
                            trip = 1;
                    }
                    //Dictionary<string, int> SationName_To_ID;
                    //if (LineID == "1")
                    //     SationName_To_ID = CONSTDATA.InitSationName_To_ID_1();
                    //else
                    //    SationName_To_ID = CONSTDATA.InitSationName_To_ID_2();

                    for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
                    {
                        //try
                        //{
                            x = rowIndex;
                            if (dt.Rows[rowIndex][trainIDIndex].ToString() == "")
                            {
                                break;
                            }
                            exTrain = dt.Rows[rowIndex][trainIDIndex].ToString();
                            exTrip = trip.ToString();
                            if (dt.Rows[rowIndex][tripIndex].ToString() == "" && dt.Rows[rowIndex][didIndex].ToString() == "")
                            {
                                dt.Rows[rowIndex][tripIndex] = trip;
                                dt.Rows[rowIndex][didIndex] = did;
                            }
                            else
                            {
                                did = Convert.ToInt32(dt.Rows[rowIndex][didIndex]);
                                string temp = dt.Rows[rowIndex][tripIndex].ToString();

                                //SationName_To_ID["ED1"] + "," +
                                //SationName_To_ID["ED2"] + "," +
                                //SationName_To_ID["TST"] + "," +
                                //SationName_To_ID["TSG"] + "," +
                                //SationName_To_ID["HKH"] + 
                                if (temp.Length > 2 && temp.Substring(1, 2) == "LT")
                                {
                                    if (lastTrip == 2 && LineID == "1")
                                        trip = Convert.ToInt32(temp.Substring(0, temp.Length - 2));     //modified 2013/8/21 针对1-T-1309 时刻表 119 列车
                                    else if (rowIndex + 2 <= dt.Rows.Count && !string.IsNullOrEmpty(dt.Rows[rowIndex+2][trainIDIndex].ToString()))
                                    {
                                        trip = Convert.ToInt32(temp.Substring(0, temp.Length - 2)); 
                                    }
                                    else
                                        trip = Convert.ToInt32(temp.Substring(0, temp.Length - 2)) - 1;        // XXLT处理
                                }
                                else if( temp == "" && did==40 && LineID == "2") { trip = lastTrip + 1; }
                                else if (temp == "" && (did == 50 || did==51 || did == 40)) { trip = lastTrip; }    // 最后一车次 LT 的处理
                                else if (temp == ""&&trip>2 && (did == 30 || (dt.Rows[rowIndex][didIndex].ToString() != "" && rowIndex + 2 < dt.Rows.Count && dt.Rows[rowIndex + 2][trainIDIndex].ToString() != "")))
                                {
                                    lastTrip++;
                                    trip = lastTrip; 
                                }    // 最后一车次 LT 的处理 30估计是不经折返线直接回库的 modified 2013/8/21
                                else if (temp != "" && LineID == "2" && dt.Rows[rowIndex][locIndex].ToString() == "STZ")
                                {
                                    trip = Convert.ToInt32(temp) - 1;
                                }
                                else if (rowIndex != 0 && temp != "") { trip = Convert.ToInt32(temp); }

                                dt.Rows[rowIndex][tripIndex] = trip;
                            }
                            lastTrip = trip;
                        //}
                        //catch (System.Exception ex)
                        //{
                        //    string err = ex.Message;
                        //}
                    }
                    tripIndex += 9;
                    didIndex += 9;
                    trainIDIndex += 9;
                    locIndex += 9;
                }

                for (int i = 0; i < columnsCount; )
                {
                    using (SqlBulkCopy bcp = new SqlBulkCopy(SQLConnString, SqlBulkCopyOptions.KeepIdentity))
                    {
                        bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                        bcp.BatchSize = 100;//每次传输的行数
                        bcp.NotifyAfter = 5;//进度提示的行数
                        bcp.DestinationTableName = TempTimeTableName;//目标表
                        int count = 0;
                        for (; count < TempTableColumnsNum; count++)
                        {
                            bcp.ColumnMappings.Add(i, columnsName[count]);
                            i++;
                        }
                        bcp.WriteToServer(dt);
                    }
                    i++;
                }

                string strSQL = string.Empty;
                //导入结束后，删除空行
                strSQL = "delete from " + TempTimeTableName + " where TrainID is null";
                sqlHelper.ExecuteSql(strSQL);

                //将Arrive,Depart,Rtime,DWell 等字段（DateTime类型）时间为秒形式写入ArriveSec,DepartSec,RtimeSec,DWellSec字段（int类型）
                UpdateSeconds(this.PickOutYYMMDD());

                strSQL = "select *,(select case when Arrive is null then Depart else Arrive end ) as shijian from " + TempTimeTableName + " order by  TrainID,convert(int,trip),shijian";
                DataTable newDT = sqlHelper.SelectDt(strSQL);

                //DataTable sddt = sqlHelper.SelectDt("select t1.trainid,t1.trip,t1.loc as start from "+TempTimeTableName+" as t1 group by t1.trainid,t1.trip where seq=max(seq)");


                InsertNewTimeTable insetTable = new InsertNewTimeTable(FileName, newDT, CSVID);
                insetTable.InsertProcessEvent += new EventHandler<InsertProcessEventArgs>(insetTable_InsertProcessEvent);
                insetTable.StartInsert();
            }
            catch (System.Exception ex)
            {
                string str = "IF EXISTS(Select 1 From Sysobjects Where Name='" + FileName + "')DROP table [" + FileName + "];";
                str += "delete from TableInfo where TableName='"+FileName+"';";
                sqlHelper.ExecuteSql(str);
                MessageBoxHelper.ShowError("从CSV导入临时表在列车" + exTrain + exTrip + "附近出现异常：" + ex.Message + ";请检查" + x + "行" + y + "列");
            }
        }


        /// <summary>
        /// 创建一个临时表，保存CSV文件的原始数据
        /// </summary>
        /// <param name="SQLConnString"></param>
        public void TransferSYCSVToDB(string SQLConnString)
        {
            try
            {
                DataTable dt = csv2dt(FilePath + "\\" + FileName + ".csv", 0);
                int columnsCount = dt.Columns.Count;
                CreatTempTable();   //如果没有临时表，创建之
                CleanTempTable();  //清空临时表数据
                string exTrain = string.Empty, exTrip = string.Empty;

                //CSV 导入DT 时由于类型不一致，会有部分数据丢失，此处作处理；并且将为空的项填满
                int trainIDIndex = 0; int tripIndex = 1; int didIndex = 2; int locIndex = 3;
                int lastTrip = 0;
                for (; tripIndex < columnsCount; )
                {
                    int did = Convert.ToInt32(dt.Rows[0][didIndex].ToString());
                    int trip;//1FT处理

                    //一般出段理解为NDOP1，但是备用车有可能从NDOP2出来，反向出段
                    if (LineID == "1")
                    {
                        if (dt.Rows[0][trainIDIndex + 3].ToString() == TorbitName[0] || dt.Rows[0][trainIDIndex + 3].ToString() == TorbitName[1])
                        {
                            trip = 2;
                        }
                        else { trip = 1; }
                    }
                    else //2号线
                    {
                        trip = 1;
                    }
                    //Dictionary<string, int> SationName_To_ID;
                    //if (LineID == "1")
                    //     SationName_To_ID = CONSTDATA.InitSationName_To_ID_1();
                    //else
                    //    SationName_To_ID = CONSTDATA.InitSationName_To_ID_2();

                    for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
                    {
                        //try
                        //{

                        if (dt.Rows[rowIndex][trainIDIndex].ToString() == "")
                        {
                            break;
                        }
                        exTrain = dt.Rows[rowIndex][trainIDIndex].ToString();
                        exTrip = trip.ToString();
                        if (dt.Rows[rowIndex][tripIndex].ToString() == "" && dt.Rows[rowIndex][didIndex].ToString() == "")
                        {
                            dt.Rows[rowIndex][tripIndex] = trip;
                            dt.Rows[rowIndex][didIndex] = did;
                        }
                        else
                        {
                            did = Convert.ToInt32(dt.Rows[rowIndex][didIndex]);
                            string temp = dt.Rows[rowIndex][tripIndex].ToString();

                            //SationName_To_ID["ED1"] + "," +
                            //SationName_To_ID["ED2"] + "," +
                            //SationName_To_ID["TST"] + "," +
                            //SationName_To_ID["TSG"] + "," +
                            //SationName_To_ID["HKH"] + 
                            if (temp.Length > 2 && temp.Substring(1, 2) == "LT")
                            {
                                if (lastTrip == 2 && LineID == "1")
                                    trip = Convert.ToInt32(temp.Substring(0, temp.Length - 2));     //modified 2013/8/21 针对1-T-1309 时刻表 119 列车
                                else if (rowIndex + 2 <= dt.Rows.Count && !string.IsNullOrEmpty(dt.Rows[rowIndex + 2][trainIDIndex].ToString()))
                                {
                                    trip = Convert.ToInt32(temp.Substring(0, temp.Length - 2));
                                }
                                else
                                    trip = Convert.ToInt32(temp.Substring(0, temp.Length - 2)) - 1;        // XXLT处理
                            }
                            else if (temp == "" && (did == 50 || did == 51 || did == 40)) { trip = lastTrip; }    // 最后一车次 LT 的处理
                            else if (temp == "" && trip > 2 && (did == 30 || (dt.Rows[rowIndex][didIndex].ToString() != "" && rowIndex + 2 < dt.Rows.Count && dt.Rows[rowIndex + 2][trainIDIndex].ToString() != "")))
                            {
                                lastTrip++;
                                trip = lastTrip;
                            }    // 最后一车次 LT 的处理 30估计是不经折返线直接回库的 modified 2013/8/21
                            else if (temp != "" && LineID == "2" && dt.Rows[rowIndex][locIndex].ToString() == "STZ")
                            {
                                trip = Convert.ToInt32(temp) - 1;
                            }
                            else if (rowIndex != 0 && temp != "") { trip = Convert.ToInt32(temp); }

                            dt.Rows[rowIndex][tripIndex] = trip;
                        }
                        lastTrip = trip;
                        //}
                        //catch (System.Exception ex)
                        //{
                        //    string err = ex.Message;
                        //}
                    }
                    tripIndex += 9;
                    didIndex += 9;
                    trainIDIndex += 9;
                    locIndex += 9;
                }

                for (int i = 0; i < columnsCount; )
                {
                    using (SqlBulkCopy bcp = new SqlBulkCopy(SQLConnString, SqlBulkCopyOptions.KeepIdentity))
                    {
                        bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                        bcp.BatchSize = 100;//每次传输的行数
                        bcp.NotifyAfter = 5;//进度提示的行数
                        bcp.DestinationTableName = TempTimeTableName;//目标表
                        int count = 0;
                        for (; count < TempTableColumnsNum; count++)
                        {
                            bcp.ColumnMappings.Add(i, columnsName[count]);
                            i++;
                        }
                        bcp.WriteToServer(dt);
                    }
                    i++;
                }

                string strSQL = string.Empty;
                //导入结束后，删除空行
                strSQL = "delete from " + TempTimeTableName + " where TrainID is null";
                sqlHelper.ExecuteSql(strSQL);

                //将Arrive,Depart,Rtime,DWell 等字段（DateTime类型）时间为秒形式写入ArriveSec,DepartSec,RtimeSec,DWellSec字段（int类型）
                UpdateSeconds(this.PickOutYYMMDD());

                strSQL = "select *,(select case when Arrive is null then Depart else Arrive end ) as shijian from " + TempTimeTableName + " order by  TrainID,convert(int,trip),shijian";
                DataTable newDT = sqlHelper.SelectDt(strSQL);

                //DataTable sddt = sqlHelper.SelectDt("select t1.trainid,t1.trip,t1.loc as start from "+TempTimeTableName+" as t1 group by t1.trainid,t1.trip where seq=max(seq)");


                InsertNewTimeTable insetTable = new InsertNewTimeTable(FileName, newDT, 3);
                insetTable.InsertProcessEvent += new EventHandler<InsertProcessEventArgs>(insetTable_InsertProcessEvent);
                insetTable.StartInsert();
            }
            catch (System.Exception ex)
            {
                MessageBoxHelper.ShowError("从CSV导入临时表出现异常：" + ex.Message);
            }
        }

        /// <summary>
        /// 将Csv读入DataTable
        /// </summary>
        /// <param name="filePath">csv文件路径</param>
        /// <param name="n">表示第n行是字段title,第n+1行是记录开始</param>
        public DataTable csv2dt(string filePath, int n)
        {
            using (StreamReader reader = new StreamReader(filePath, System.Text.Encoding.UTF8, false))
            {
                DataTable dt = new DataTable();
                DateTime dtDate;
                //int num2;
                int i = 0, m = 0;
                reader.Peek();
                while (reader.Peek() > 0)
                {
                    m = m + 1;
                    int a = 0;
                    //int b=0;
                    string str = reader.ReadLine();
                    if (m == n + 1)
                    {
                        string[] split = str.Split(',');
                        int num = GetFirstNull(split) + 1;
                        for (int j = 0; j < split.Length; j++)
                        {
                            if (j / num > a)
                                a = j / num;
                            if (a != 0)
                            {
                                if (split[j] != "")
                                    dt.Columns.Add(split[j] + (j / num));
                                else
                                    dt.Columns.Add("NoName" + (j / num));
                            }
                            else
                            {
                                if (split[j] != "")
                                    dt.Columns.Add(split[j]);
                                else
                                    dt.Columns.Add("NoName");
                            }
                        }
                    }
                    else
                    {
                        string[] split = str.Split(',');
                        System.Data.DataRow dr = dt.NewRow();
                        for (i = 0; i < split.Length; i++)
                        {
                            if (DateTime.TryParse(split[i], out dtDate))
                            {
                                dr[i] = DateTime.Parse(split[i]);
                            }
                            else if (split[i] == "")
                            {
                                dr[i] = DBNull.Value;
                            }
                            else
                            {
                                if (split[i].Contains("FT") || split[i].Contains("LT"))
                                {
                                    dr[i] = DBNull.Value;
                                }
                                //if (i == 1 + b * 9)
                                //{
                                //    if (int.TryParse(split[i], out num2))
                                //    {
                                //        dr[i] = split[i];
                                //        b++;
                                //    }
                                //    else
                                //    {
                                //        dr[i] = DBNull.Value;
                                //    }
                                //}
                                else
                                {
                                    dr[i] = split[i];
                                }
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
                reader.Close();
                return dt;
            }
        }

        private int GetFirstNull(string[] arr)
        {
            for (int i = 0; i < arr.Length; i++)
            {
                if (arr[i] == "")
                {
                    return i;
                }
            }
            return arr.Length;
        }


        void insetTable_InsertProcessEvent(object sender, InsertProcessEventArgs e)
        {
            //throw new NotImplementedException();
            OnInsertProcess(e.State);
            if (Convert.ToInt32(e.State) >= 100)
            {
                OnInsertFinish();
            }
        }

        /// <summary>
        /// 更新Arrive,Depart,Rtime,DWell 等字段时间为秒形式
        /// </summary>
        private void UpdateSeconds(string YY_MM_DD)
        {
            string strSQL = "update " + TempTimeTableName + " SET ArriveSec = datediff(s,DATEADD(hh,0,'" + YY_MM_DD + "'),Arrive),"
                 + "DepartSec = datediff(s,DATEADD(hh,0,'" + YY_MM_DD + "'),Depart),"
                 + "RtimeSec = datediff(s,DATEADD(hh,0,'" + YY_MM_DD + "'),Rtime),"
                 + "DWellSec = datediff(s,DATEADD(hh,0,'" + YY_MM_DD + "'),DWell)";

            try
            {
                sqlHelper.ExecuteSql(strSQL);
            }
            catch (System.Exception ex)
            {
                //MessageBox.Show("DateTime转换Seconds时异常！");
            }
        }

        /// <summary>
        /// 提取出字段Arrive等形式如“1899-12-30 7:00:27  ” 中的"1899-12-30 "部分
        /// </summary>
        /// <returns></returns>
        private string PickOutYYMMDD()
        {
            //找出一条记录，拿到日期
            string strSQL = "select top 1 Arrive  from " + TempTimeTableName + " where Arrive is not null ";
            string time = Convert.ToString(sqlHelper.GetSingle(strSQL));
            string[] yy_mm_dd = time.Split(' ');
            return yy_mm_dd[0];
        }

        /// <summary>
        /// 建新的临时表
        /// </summary>
        private void CreatTempTable()
        {
            string strSQL = string.Format("if object_id('{0}') is null create table {0}(", TempTimeTableName);
            strSQL += "TrainID varchar(30),Trip varchar(30),DID varchar(30),LOC varchar(30),Arrive datetime,Depart datetime,Rtime datetime,DWell datetime,";
            strSQL += "ArriveSec int,DepartSec int,RtimeSec int,DWellSec int" + ")";

            sqlHelper.ExecuteSql(strSQL);
        }

        /// <summary>
        /// 清空临时表的所有数据
        /// </summary>
        private void CleanTempTable()
        {
            string strSQL = "select count(*) from " + TempTimeTableName;
            int count = Convert.ToInt32(sqlHelper.GetSingle(strSQL));
            if (count > 0)
            {
                string strSQL2 = "delete from " + TempTimeTableName;
                sqlHelper.ExecuteSql(strSQL2);
            }
        }

        private string[] GetTorbit()
        {
            switch (LineID)
            {
                case"1":
                    return new string[]{"WD1","WD2"};
                case"2":
                    return new string[]{"ED1","ED2"};
            }
            return null;
        }

        private string GetCsvConnStr(string path)
        {
            //string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=";
            string strConn = @"DRIVER={Microsoft Access Text Driver (*.txt, *.csv)};Dbq=";
            strConn += path;//这个地方只需要目录就可以了                                                      
            strConn += ";Extensions=asc,csv,tab,txt;";

            return strConn;
        }

        void bcp_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
        }

        private void OnInsertFinish()
        {
            EventHandler handler = this.InsertFinishedEvent;
            if (handler != null)
            {
                handler(this, null);
            }
        }

        private void OnInsertProcess(int state)
        {
            EventHandler<InsertProcessEventArgs> handler = this.InsertProcessEvent;
            if (handler != null)
            {
                InsertProcessEventArgs e = new InsertProcessEventArgs(state);
                handler(this, e);
            }
        }
    }

    class InsertNewTimeTable
    {
        private string LineID = CONSTDATA.UserModel.Line;  

        private DataTable TempTable { get; set; }

        private string TimeTableName { get; set; }

        private int CSVCLASS { get; set; }

        private Dictionary<string, int> SationName_To_ID;
        private Dictionary<int, string> StationID_To_Name;

        private static int UpDirection = 2;
        private static int DownDirection = 1;

        /// <summary>
        /// 该时刻表最早时间，用于凌晨后运行车辆的Arrive和Depart更新
        /// </summary>
        private int allBeginTime = -1 ;

        public event EventHandler<InsertProcessEventArgs> InsertProcessEvent;

        private SqlDataLayer sqlHelper = new SqlDataLayer();

        public InsertNewTimeTable(string timeTableName, DataTable tempDT, int CSVID)
        {
            this.TempTable = tempDT;

            this.TimeTableName = timeTableName;

            this.CSVCLASS = CSVID;

            if (LineID == "1")
            {
                SationName_To_ID = CONSTDATA.InitSationName_To_ID_1();
                StationID_To_Name = CONSTDATA.InitStationID_To_Name_1();
            }
            else
            {
                SationName_To_ID = CONSTDATA.InitSationName_To_ID_2();
                StationID_To_Name = CONSTDATA.InitStationID_To_Name_2();
            }
        }

        public bool StartInsert()
        {

            bool result = false;
            int count = 0;
            if (CheckHasTable()) { return result; }
            if (!CreateTable()) { return result; }

            
            if (TempTable != null)
            {

                
                for (int i = 0; i < TempTable.Rows.Count; i++)
                {
                    int start = -1;     //起始站
                    int destiny = -1;   //目的地码
                    string lastID = string.Empty; //趟数
                    int direction = -1;   //上行为双数，下行为单数
                    string DID = string.Empty;
                    string trainID = string.Empty;  //车次号
                    int stationID = -1;//当前站ID
                    int arrive = -1;
                    int depart = -1;
                    string firstID = string.Empty;  //车体号
                    string arc_ne = "-----";//接班车
                    int stop = -1;

                    string lastTrain = string.Empty;
                    int lastStart=0, lastDestiny=0 ;

                    int DWell = -1;
                    int Rtime = -1;
                    if (TempTable.Rows[i]["DWellSec"].ToString().Trim() != "")
                    {
                        DWell = Convert.ToInt32(TempTable.Rows[i]["DWellSec"].ToString().Trim());
                    }
                    if (TempTable.Rows[i]["RTimeSec"].ToString().Trim() != "")
                    {
                        Rtime = Convert.ToInt32(TempTable.Rows[i]["RTimeSec"].ToString().Trim());
                    }

                    string cheti = TempTable.Rows[i]["TrainID"].ToString().Trim(); 
                    //获取当前站ID
                    string loc = TempTable.Rows[i]["LOC"].ToString().Trim();
                    SetLocationID(loc, ref stationID);

                    //设置每一行的趟数和方向
                    string trip = TempTable.Rows[i]["Trip"].ToString().Trim();



                    SetLastID(trip, ref lastID, ref direction);

                    trainID = TempTable.Rows[i]["TrainID"].ToString().Trim() + lastID;

                    string toColsStr = "(";
                    if (LineID == "1")
                    {
                        toColsStr += "'TLM','TSH','WD1','WD2','LMG'";
                    }
                    else
                    {
                        toColsStr += "'ED1','ED2','TST','PTT','TSG','HKH','ZYY','QYL','LND','TPT'";
                    }
                    toColsStr += ")";


                    if (TempTable.Rows[i]["ArriveSec"].ToString() == "") { arrive = 0; }  //Arrive
                    else { arrive = Convert.ToInt32(TempTable.Rows[i]["ArriveSec"]); }

                    if (TempTable.Rows[i]["DepartSec"].ToString() == "") { depart = -1; }//Depart
                    else { depart = Convert.ToInt32(TempTable.Rows[i]["DepartSec"]); }

                    firstID = TempTable.Rows[i]["TrainID"].ToString().Trim();
                    DID = TempTable.Rows[i]["DID"].ToString().Trim();

                    if (lastTrain != trainID)
                    {
                        //DataRow[] rows;
                        //rows = TempTable.Select("TrainID='" + cheti + "' and Trip='" + trip + "' and ArriveSec is null and LOC in " + toColsStr);
                        string sql = "select *,(select case when Arrive is null then Depart else Arrive end ) as shijian from " + CSVHelper.TempTimeTableName + " where TrainID='" + cheti + "' and Trip='" + trip + "' and ArriveSec is null and LOC in " + toColsStr+" order by  TrainID,convert(int,trip),shijian";
                        DataTable qidianDt = sqlHelper.SelectDt(sql);
                        if (qidianDt.Rows.Count ==0)
                        {
                            MessageBoxHelper.ShowError("车次" + trainID + "没有找到起点站");
                            string delSql = "delete from "+TimeTableName;
                            sqlHelper.ExecuteSql(delSql);
                            return false;
                        }
                        //SetLocationID(rows[0]["LOC"].ToString(),ref start);
                        //start = StationModel.GetByCode(GlobalVariables.StationList, rows[0]["LOC"].ToString()).Id;
                        start = SationName_To_ID[qidianDt.Rows[0]["LOC"].ToString()];


                        //rows = TempTable.Select("TrainID='" + cheti + "' and Trip='" + trip + "' and DepartSec is null and LOC in " + toColsStr);
                        sql = "select *,(select case when Arrive is null then Depart else Arrive end ) as shijian from " + CSVHelper.TempTimeTableName + " where TrainID='" + cheti + "' and Trip='" + trip + "' and DepartSec is null and LOC in " + toColsStr + " order by  TrainID,convert(int,trip),shijian";
                        qidianDt = sqlHelper.SelectDt(sql);
                        if (qidianDt.Rows.Count == 0)
                        {
                            MessageBoxHelper.ShowError("车次" + trainID + "没有找到终点站");
                            string delSql = "delete from " + TimeTableName;
                            sqlHelper.ExecuteSql(delSql);
                            return false;
                        }
                        //SetLocationID(rows[0]["LOC"].ToString(), ref destiny);
                        //destiny = StationModel.GetByCode(GlobalVariables.StationList, rows[0]["LOC"].ToString()).Id;
                        destiny = SationName_To_ID[qidianDt.Rows[qidianDt.Rows.Count-1]["LOC"].ToString()];
                    }
                    else
                    {
                        start = lastStart;
                        destiny = lastDestiny;
                    }
                    lastTrain = trainID;
                    lastStart = start;
                    lastDestiny = destiny;

                    //////起始站
                    //SetStart(trip, loc, ref start);
                    ////终点站
                    //SetDestiny(trip, DID, ref destiny);
                    //设置Stop属性
                    SetStop(arrive, depart, DWell, ref stop);
                    //设置arc_ne属性
                    //SetArc_ne(trainID, loc, depart, DID, ref arc_ne);
                    if (stationID == destiny)
                    {
                        //if (lastID == "09")
                        //{
                        //    int pdd = 1 + 0;
                        //}
                        if (i < TempTable.Rows.Count - 1 && TempTable.Select("trainID='"+ cheti + "' and Trip='" + (Convert.ToInt32(trip) + 1) + "'").Length>0)
                        {

                            arc_ne = (Convert.ToInt32(trainID) + 1).ToString();
                        }
                        else
                        {
                            arc_ne = "#####";
                        }
                    }


                    //插入数据库
                    if (!InserToTable(trainID, stationID, start, direction, arrive, depart, firstID, lastID, arc_ne, stop, destiny, DID, Rtime))
                    {
                        MessageBox.Show("插入数据库表失败!");
                        return result;
                    }
                    count++;
                    this.OnInsertProcess(count * 100 / TempTable.Rows.Count - 1);
                }
                //UpdateBeiYongStart();
                //UpdateDestiny();
                UpdateArcne();
                UpdateStartAndDestiny();
                

                AddNewRecord(); //添加一条成功插入后的记录

                //UpdateArriveDepart();

                AddTrainDetailInfo();

                OnInsertProcess(100);  //此处才算完成
                result = true;
            }
            return result;
        }

        //因为备用车有可能从NDOP1开出，也有可能从NDOP2开出，当从NDOP2开出时，需要修改  modifed 9/29
        private void UpdateBeiYongStart()
        {
            int NDOP2;
            SationName_To_ID.TryGetValue("NDOP2", out NDOP2);
            bool update = false;
            string strSql = "select * from " + TimeTableName + " where Did = '96' order by Arrive,Depart asc";
            DataTable dt = sqlHelper.SelectDt(strSql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["Station"].ToString() == NDOP2.ToString()) { update = true; break; }
            }
            if (update)
            {
                string str = "update " + TimeTableName + " set start = " + NDOP2 + " where midid in (select midid from " + TimeTableName + " where did ='96' and Station = " + NDOP2 + ")";
                sqlHelper.ExecuteSql(str);
            }
        }

        //DID为50的车属于回段，该躺车都以NDOP2（67）为目的码
        private void UpdateDestiny()
        {
            int des;
            SationName_To_ID.TryGetValue("NDOP2", out des);

            string strSQL1 = "update " + TimeTableName + " Set Destiny =" + des + "where TrainID in (select distinct TrainID from " + TimeTableName + " where DID = '50' or did='51' )";
            string strSQL2 = "update " + TimeTableName + " Set arc_ne = '-----' where TrainID in (select distinct TrainID from " + TimeTableName + " where DID = '50' or did='51' ) and arc_ne <> '#####'";

            sqlHelper.ExecuteSql(strSQL1);
            sqlHelper.ExecuteSql(strSQL2);
        }

        private void AddTrainDetailInfo()
        {
            string sql_GetTimeTableID = "select ID from TableInfo where TableName = '" + TimeTableName + "'";  //获取该时刻表在TableInfo中的ID
            string id = sqlHelper.GetSingle(sql_GetTimeTableID).ToString();
            List<string> AddedTrainList = new List<string>();

            int lastStaID = -1;
            if (LineID == "1")
                lastStaID = 22; //一号线末站ID
            else
                lastStaID = 19; //预设二号线末站ID为TSt

            if (!string.IsNullOrEmpty(id))
            {
                string sql_GetAllDistinctTrainId = "select distinct TrainID,DID,LastID,Destiny,Start from [" + TimeTableName + "] order by TrainID";
                DataTable distinctTrainDT = sqlHelper.SelectDt(sql_GetAllDistinctTrainId);

                if (null != distinctTrainDT && distinctTrainDT.Rows.Count > 0)
                {
                    ArrayList sqlList = new ArrayList();
                    string sqlInsert = "";
                    for (int i = 0; i < distinctTrainDT.Rows.Count; i++)
                    {
                        string trainid = distinctTrainDT.Rows[i]["TrainID"].ToString();
                        if (AddedTrainList.Contains(trainid))
                            continue;

                        string lastID = distinctTrainDT.Rows[i]["LastID"].ToString();
                        string DID = distinctTrainDT.Rows[i]["DID"].ToString();
                        string start = distinctTrainDT.Rows[i]["Start"].ToString();
                        int rest = Convert.ToInt32(trainid) % 2;
                        //2号线，始发站为中医药的备班车特殊处理
                        if (LineID == "2" && start == "15")
                            rest = 1;

                        int beginTime = -1;
                        int endTime = -1;

                        if (LineID == "2")
                        {
                            string destiny = distinctTrainDT.Rows[i]["Destiny"].ToString();
                            if (destiny == "30") // TST
                                lastStaID = 19; //三台子站
                            else if (destiny == "32")
                                lastStaID = 26;
                            else if (destiny == "22") //航空航天
                                lastStaID = 22;
                            else if (destiny == "33")
                                lastStaID = 26;
                            else if (destiny == "1")
                                lastStaID = 1;
                            else if (destiny == "23")
                                lastStaID = 23;
                        }

                        if (LineID == "1" && lastID == "02")//出库车
                            beginTime = (int)GlobalVariables.GetStartTime(TimeTableName, trainid, 0); //取出库时间
                        //beginTime = (int)GlobalVariables.GetDepartTimeFromSta(TimeTableName, trainid, 1, 0); //出库时间更改为首站的离开时间，考虑到司机提前出车的问题
                        else if (LineID == "2" && lastID == "01")
                            beginTime = (int)GlobalVariables.GetStartTime(TimeTableName, trainid, 0); //取出库时间
                        //beginTime = (int)GlobalVariables.GetDepartTimeFromSta(TimeTableName, trainid, 1, 0);//出库时间更改为首站的离开时间，考虑到司机提前出车的问题
                        else
                        {
                            if (LineID == "1")
                            {
                                if (rest == 1)
                                    beginTime = (int)GlobalVariables.GetArriveTimeFromSta(TimeTableName, trainid, 1, 0); //取首站到达时间
                                else
                                    beginTime = (int)GlobalVariables.GetDepartTimeFromSta(TimeTableName, trainid, 1, 0); //取首站离开时间
                            }
                            else
                            {
                                if (rest == 1)
                                    beginTime = (int)GlobalVariables.GetDepartTimeFromSta(TimeTableName, trainid, 1, 0); //取首站离开时间
                                else
                                    beginTime = (int)GlobalVariables.GetArriveTimeFromSta(TimeTableName, trainid, 1, 0); //取首站到达时间
                            }
                        }

                        if (DID != "30" && DID != "50" && DID != "51" && DID != "40" ) //不是回库车
                        {
                            if (LineID == "1")
                            {
                                if (rest == 1)
                                    endTime = (int)GlobalVariables.GetDepartTimeFromSta(TimeTableName, trainid, lastStaID, 0); //取末站的离开时间
                                else
                                    endTime = (int)GlobalVariables.GetArriveTimeFromSta(TimeTableName, trainid, lastStaID, 0); //取末站的到达时间
                            }
                            else
                            {
                                if (rest == 1)
                                    endTime = (int)GlobalVariables.GetArriveTimeFromSta(TimeTableName, trainid, lastStaID, 0); //取末站的到达时间
                                else if(GlobalVariables.GetDepartTimeFromSta(TimeTableName, trainid, lastStaID, 0) == null)
                                    endTime = (int)GlobalVariables.GetFinishTime(TimeTableName, trainid, 0);
                                else
                                    endTime = (int)GlobalVariables.GetDepartTimeFromSta(TimeTableName, trainid, lastStaID, 0); //取末站的离开时间
                            }

                        }
                        else
                        {
                            //beginTime = (int)GlobalVariables.GetArriveTimeFromSta(TimeTableName, trainid, lastStaID, 0); //取出库时间，如果是回库车，此处需要更新beginTime

                            beginTime = (int)GlobalVariables.GetDepartTimeFromSta(TimeTableName, trainid, lastStaID, 0);

                            endTime = (int)GlobalVariables.GetFinishTime(TimeTableName, trainid, 0);
                        }

                        if (beginTime > endTime)
                            this.SwapTime(ref beginTime, ref endTime);  //交换以使beginT < endT

                        int Interval = endTime - beginTime;

                        if (i == 0) { allBeginTime = beginTime; UpdateArriveDepart(); }  //用第一个车的发车时间作为全天开始运营时间

                        if (beginTime < allBeginTime)
                            beginTime = beginTime + 86400;

                        if (endTime < allBeginTime)
                            endTime = endTime + 86400;

                        sqlInsert = "insert into TrainDetailInfo values('" + id + "','" + trainid + "','" + beginTime + "','" + endTime + "','" + Interval + "')";

                        sqlList.Add(sqlInsert);

                        AddedTrainList.Add(trainid);
                    }
                    sqlHelper.ExecuteSqlTran(sqlList);
                }
            }
        }

        private void SwapTime(ref int TimeA,ref int TimeB)
        {
            int temp = TimeA;
            TimeA = TimeB;
            TimeB = temp;
        }

        /// <summary>
        /// 更新凌晨12点后运行车次的Arrive和Depart
        /// </summary>
        private void UpdateArriveDepart()
        {
            if (allBeginTime == -1)
                return;

            string sql_updateArrive = "update [" + TimeTableName + "] set Arrive = Arrive + " + 86400 + " where Arrive <>0 and Arrive < " + allBeginTime;

            string sql_updateDepart = "update [" + TimeTableName + "] set Depart = Depart + " + 86400 + " where Depart <>-1 and Depart < " + allBeginTime;

            ArrayList arr = new ArrayList() { sql_updateArrive, sql_updateDepart };

            sqlHelper.ExecuteSqlTran(arr);
        }
        public void UpdateStartAndDestiny()
        {
            if (LineID == "2")
            {
                ArrayList startStationLst = new ArrayList();
                startStationLst.Add(SationName_To_ID["ED1"]);
                startStationLst.Add(SationName_To_ID["ED2"]);
                startStationLst.Add(SationName_To_ID["TST"]);
                startStationLst.Add(SationName_To_ID["PTT"]);
                startStationLst.Add(SationName_To_ID["TSG"]);


                string sql = "update t1 set t1.start=(select t2.station from [" + TimeTableName + "] as t2 where t2.TrainID=t1.TrainID and Arrive=0 and t2.Station in ("+
                    SationName_To_ID["ED1"] + "," +
                    SationName_To_ID["ED2"] + "," +
                    SationName_To_ID["TST"] + "," +
                    SationName_To_ID["PTT"] + "," +
                    SationName_To_ID["TPT"] + "," +
                    SationName_To_ID["TSG"] + "," +
                    SationName_To_ID["HKH"] + "," +
                    SationName_To_ID["ZYY"] + 
                    ")),t1.Destiny=(select t3.station from [" + TimeTableName + "] as t3 where t3.TrainID=t1.TrainID and Depart=-1 and arc_ne <> '-----' and t3.Station in (" +
                    SationName_To_ID["ED2"] + "," +
                    SationName_To_ID["TST"] + "," +
                    SationName_To_ID["PTT"] + "," +
                    SationName_To_ID["TPT"] + "," +
                    SationName_To_ID["TSG"] + "," +
                    SationName_To_ID["HKH"] + "," +
                    SationName_To_ID["ZYY"] +
                    ") ) from [" + TimeTableName + "] as t1";
                sqlHelper.ExecuteSql(sql);
            }
            

        }
        ////更新出库车的arc_nc ，由于出库车在第一个站时，会添加错误的arc_ne 字段
        private void UpdateArcne()
        {
            //string strSQL = "";
        }

        private bool CheckHasTable()
        {
            string strSQL = "select * from TableInfo where TableName='" + TimeTableName + "'";
            try
            {
                if (sqlHelper.ExecuteSql(strSQL) > 0)
                {
                    MessageBox.Show("已经存在相同名字的表！");
                    return true;
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return true;
            }
            return false;
        }

        private bool CreateTable()
        {
            string strSQL = "create table [" + TimeTableName + "] (TrainID nvarchar(10),Station int,Start int,Direction int," +
                                    "Arrive int,Depart int,MidID nvarchar(10),LastID nvarchar(10),arc_ne nvarchar(10),Stop int," +
                                    "Destiny int ,DID nvarchar(10),RTime int)";
            if (!sqlHelper.CreateTable(strSQL))
            {
                MessageBox.Show("执行创建数据库失败！有可能是表名已经存在，请确认删除！");
                return false;
            }
            return true;
        }

        private bool InserToTable(string TrainID, int Station, int Start, int Direction, int Arrive, int Depart, string FirstID, string LastID, string arc_ne, int Stop, int Destiny, string DiD, int RTime)
        {
            string InsertStr = "insert into [" + TimeTableName + "] values ('" + TrainID + "','" + Station + "','" + Start +
                   "','" + Direction + "','" + Arrive + "','" + Depart + "','" + FirstID + "','" + LastID +
                   "','" + arc_ne + "','" + Stop + "','" + Destiny + "','" + DiD + "','" + RTime + "')";
            if (sqlHelper.ExecuteSql(InsertStr) != 1)
            {
                return false;
            }
            return true;
        }

        private void AddNewRecord()
        {
            string IndexInsert = "insert into TableInfo (TableName,InsertTime,LineID,TableClass) values ('" + TimeTableName +
                                 "','" + DateTime.Now.ToString() + "','" + LineID + "','" + CSVCLASS + "')";
            if (sqlHelper.ExecuteSql(IndexInsert) <= 0)
            {
                MessageBox.Show("索引数据库表TableInfo行插入失败！");
                return;
            }
        }

        //设置终点处交接班车次号
        private void SetArc_ne(string trainID, string loc, int depart, string did, ref string arc_ne)
        {
            string end1 = "TLM";
            string end2 = "TSH";
            string end3 = string.Empty;
            if (LineID == "2")
            {
                end1 = "TST";
                end2 = "TSG";
                end3 = "HKH";
            }

            if (depart == -1)
            {
                if ((did == "1"||did=="4") && (loc == end1 || loc == end2 || loc == end3))
                {
                    arc_ne = (Convert.ToInt32(trainID) + 1).ToString();
                }
                else if ((loc == "WD2" || loc == "ED2") && (did == "50" || did=="51"))  //回段 WD2 为1号线逻辑，ED2 为2号线逻辑
                {
                    arc_ne = "#####";
                }
            }
        }

        //设置Stop字段
        private void SetStop(int arrive, int depart, int DWell, ref int stop)
        {
            if (arrive == 0 || DWell == 0) { stop = 1; }
            else if (depart == -1) { stop = 2; }
            else { stop = 0; }
        }

        private void SetDestiny(string trip, string did, ref int destiny)
        {
            int remainder = Convert.ToInt32(trip) % 2;

            if (LineID == "1")
            {

                if (did == "50" || did == "30"||did=="51") { SationName_To_ID.TryGetValue("WD2", out destiny); } //回段车
                else if (remainder == 1 && (did == "1"||did=="4"))     //奇数趟,did为1的列车以十三号街返轨线为终点
                {
                    SationName_To_ID.TryGetValue("TSH", out destiny);
                }
                else if (remainder == 0 && (did == "1"||did=="4")) { SationName_To_ID.TryGetValue("TLM", out destiny); } //偶数趟,did为1的列车以黎明广场折返轨为终点
            }
            else  //2号线逻辑
            {

                if (did == "50" || did == "30" ||did=="51") { SationName_To_ID.TryGetValue("ED2", out destiny); } //回段车
                else if (remainder == 1 && (did == "1"||did=="4"))     //奇数趟,did为1的列车以三台子返轨线为终点
                {
                    SationName_To_ID.TryGetValue("TST", out destiny);
                }
                else if (remainder == 0 && (did == "1"||did=="4")) { SationName_To_ID.TryGetValue("TSG", out destiny); } //偶数趟,did为1的列车以TSG折返轨为终点
            }
        }

        private void SetStart(string trip, string loc, ref int start)
        {
            if (LineID == "1")
            {
                if (Convert.ToInt32(trip) == 1)//最后一班车从HZX出发，回段
                {
                    //SationName_To_ID.TryGetValue("HZX", out start);
                }
                else if (Convert.ToInt32(trip) % 2 == 1) //除1 外的奇数车都从黎明广场折返线出发
                {
                    SationName_To_ID.TryGetValue("TLM", out start);
                }
                else if (trip == "2") //第一趟车出段判断
                {
                    if (loc == "WD1")
                    {
                        SationName_To_ID.TryGetValue("WD1", out start);  //WD1
                    }
                    else { SationName_To_ID.TryGetValue("WD2", out start); }
                }
                else { SationName_To_ID.TryGetValue("TSH", out start); }  //其他偶数趟都是从十三号街返轨出段
            }
            else   //2号线逻辑
            {
                if (trip == "1")  //第一趟有车从 ED1 或 ED2 发车
                {
                    if (loc == "ED1")
                    {
                        SationName_To_ID.TryGetValue("ED1", out start);  //WD1
                    }
                    else { SationName_To_ID.TryGetValue("ED2", out start); }
                }
                else if (Convert.ToInt32(trip) % 2 == 0)  //偶数趟都从三台子折返线发车
                {
                    SationName_To_ID.TryGetValue("TST", out start);
                }
                else { SationName_To_ID.TryGetValue("TSG", out start); } // 其他都从TSG折返线出车
            }
        }

        private void SetLocationID(string loc, ref int stationID)
        {
            if (!SationName_To_ID.TryGetValue(loc, out stationID))
            {
                MessageBox.Show("找不到指定loc对应的站ID：" + loc);
            }
        }

        //设置每一行的趟数,并根据形式的趟数来确认方向，lastID为偶数时是上行（2）否则为下行（1）
        private void SetLastID(string trip, ref string lastID, ref int direcion)
        {
            int IntTrip = Convert.ToInt32(trip);
            if (IntTrip % 2 == 0)
            {
                direcion = 2;
            }
            else { direcion = 1; }
            lastID = (IntTrip + 100).ToString().Substring(1, 2);
        }

        private void OnInsertProcess(int state)
        {
            EventHandler<InsertProcessEventArgs> handler = this.InsertProcessEvent;
            if (handler != null)
            {
                InsertProcessEventArgs e = new InsertProcessEventArgs(state);
                handler(this, e);
            }
        }
    }

    class InsertProcessEventArgs : EventArgs
    {
        public int State { get; set; }

        public InsertProcessEventArgs(int state) { this.State = state; }
    }
}